const moment = require('moment')
const { mysqlPool } = require('../config/db')
const { handleCommentData } = require('../utils/lib')

class Article {
    // 查询文章分类列表
    // 0 草稿，1 待审核，2 审核通过，3 已发布，4 审核拒绝
	static async getClassifyList() {
		const [result] = await mysqlPool.execute(
			'SELECT * FROM classify'
		)
		return result
	}

	// 添加文章
	static async addArticle({ title, abstract, content, classifyId, userId, state, coverUrl }) {
        let createTime = moment().format('YYYY-MM-DD HH:mm:ss')
        let params = [title, abstract, content, classifyId, userId, state, createTime]
        let sql = ''

        // 查询这个用户是否存在
        let sql2 = 'SELECT * FROM users WHERE id = ?'
        const [result2] = await mysqlPool.execute(sql2, [userId])

        if (!result2 || result2.length === 0) {
            return { code: 500, data: null, msg: '用户不存在，文章添加失败！' }
        }

        if (coverUrl) {
            params.push(coverUrl)
            sql = 'INSERT INTO article (title, abstract, content, classifyId, userId, state, createTime, coverUrl) VALUES (?, ?, ?, ?, ?, ?, ?, ?)'
        } else {
            sql = 'INSERT INTO article (title, abstract, content, classifyId, userId, state, createTime) VALUES (?, ?, ?, ?, ?, ?, ?)'
        }

		const [result] = await mysqlPool.execute(sql, params)

		return result
	}

	// 删除文章
	static async delArticle(userId, articleId) {
        // 1、查询用户文章是否存在，如果存在，将用户信息查询出来
        let sql1 = `SELECT * FROM article WHERE userId = ? AND id = ?`
        let [result1] = await mysqlPool.execute(sql1, [userId, articleId])
        
        if (!result1) {
            return { code: 500, data: null, msg: '用户文章不存在！' }
        } else {
            // 2、查询文章是否为草稿、审核通过、审核拒绝状态，如果不是，无法删除
            let state = result1[0].state
            
            if (state === 0 || state === 2 || state === 3) {
                // 3、删除文章
		        const [result] = await mysqlPool.execute('DELETE FROM article WHERE id = ?', [articleId])
                return result
            } else {
                // 4、不能直接删除已发布、待审核的文章
                return { code: 500, data: null, msg: '此文章不可删除！' }
            }
        }
	}

	// 查询文章列表
	static async getArticleList(currentPage, pageSize, classifyId, keyword) {
        let state = 3 // 只能查询到已经发布的文章
        let params = [state]
        let sql2 = `SELECT COUNT(*) AS total FROM article a WHERE state = ?`
		let sql1 = `
			SELECT
				a.id AS articleId,
				a.title,
				a.state,
				a.userId,
                a.classifyId,
                a.abstract,
                a.likeCount,
                a.viewerCount,
                a.coverUrl,
                Date_Format(a.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                u.id AS userId,
                u.nickName,
                u.username
			FROM
                article a
            INNER JOIN
                users u ON a.userId = u.id
            WHERE
                a.state = ?
		`

        // 如果存在分类id
        if (classifyId && JSON.parse(classifyId) !== 0) {
            sql1 += ` AND a.classifyId = ?`
            sql2 += ` AND a.classifyId = ?`
            params.push(classifyId)
        }

        if (keyword) {
            sql1 += ` AND (? IS NULL OR (a.title LIKE CONCAT('%', ?, '%') OR a.abstract LIKE CONCAT('%', ?, '%')))`
            sql2 += ` AND (? IS NULL OR (a.title LIKE CONCAT('%', ?, '%') OR a.abstract LIKE CONCAT('%', ?, '%')))`
            params.push(keyword, keyword, keyword)
        }

        sql1 += ` ORDER BY a.createTime DESC`

        // 如果分页条件存在就执行分页查询，否则查询所有
        if (currentPage && pageSize) {
            sql1 += ` LIMIT ${(currentPage - 1) * pageSize}, ${pageSize}`
        }

		let [data1] = await mysqlPool.execute(sql1, params)
        let [data2] = await mysqlPool.execute(sql2, params)
		let result = await Promise.all([data1, data2])
        let data = { rows: result[0], total: result[1][0].total }

		return data
	}

    // 查询文章详情
    static async getArticleDetail(articleId, userId) {
        // 查询文章详情、用户信息、用户粉丝数量、用户文章数量、当前用户所有文章浏览量总和
        let sql = `
            SELECT
                u.username,
                u.nickName,
                u.avatar,
                u.job,
                a.title,
                a.content,
                DATE_Format(a.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                a.userId,
                a.state,
                a.likeCount,
                a.viewerCount,
                a.classifyId,
                a.abstract,
                a.coverUrl,
                (SELECT COUNT(*) FROM article WHERE userId = u.id) AS articleCount,
                (SELECT COUNT(*) FROM follow WHERE followId = u.id) AS followCount,
                (SELECT CAST(COALESCE(SUM(viewerCount), 0) AS UNSIGNED) FROM article WHERE userId = u.id) AS viewerTotalCount
            FROM
                users u
            LEFT JOIN
                article a ON u.id = a.userId AND a.id = ?
            LEFT JOIN
                follow f ON u.id = f.followId
            WHERE
                u.id = ?
        `
        
        let [result] = await mysqlPool.execute(sql, [articleId, userId])

        return result[0]
    }

    // 添加文章观看次数（只有登录了，才添加）
    static async addViewCount(articleId, userId) {
        // 查询用户是否观看过该文章
        const [result1] = await mysqlPool.execute(
            'SELECT COUNT(*) AS count FROM viewer WHERE articleId = ? AND userId = ?',
            [articleId, userId]
        )

        // 表示该用户已经观看过该文章
        if (result1[0].count > 0) {
            return { code: 500, msg: '该用户已经观看过该文章' }
        }

        // 否则没有看过，那么往观看记录表添加数据，并且修改文章的观看次数
        const [result2] = await mysqlPool.execute(
            'INSERT INTO viewer (articleId, userId) VALUES (?, ?)',
            [articleId, userId]
        )

        const [result3] = await mysqlPool.execute(
            'UPDATE article SET viewerCount = viewerCount + 1 WHERE id = ?',
            [articleId]
        )

        const result = await Promise.all([result2, result3])

        return result
    }

    // 查询用户文章列表
    // 可以根据状态查询：state 0 草稿，1 待审核，2 审核通过，3 已发布，4 审核拒绝，5 已下架
    static async getUserArticleList(userId, state, currentPage, pageSize) {
        let params = []
        let sql1 = `
            SELECT
                a.id AS articleId,
                a.title,
                a.state,
                a.userId,
                a.classifyId,
                a.abstract,
                a.likeCount,
                a.viewerCount,
                a.content,
                a.coverUrl,
                Date_Format(a.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                u.id AS userId,
                u.nickName,
                u.username
			FROM
                article a
            INNER JOIN
                users u ON a.userId = u.id
        `

        let sql2 = `
            SELECT
                COUNT(*) AS total
            FROM
                article
        `
        params.push(userId)

        if (state !== undefined && state !== null && state !== '') {
            params.push(JSON.parse(state))
            sql1 += ` WHERE a.userId = ? AND a.state = ? ORDER BY a.createTime DESC LIMIT ${(currentPage - 1) * pageSize}, ${pageSize}`
            sql2 += ` WHERE userId = ? AND state = ?`
        } else {
            sql1 += ` WHERE a.userId = ? ORDER BY a.createTime DESC LIMIT ${(currentPage - 1) * pageSize}, ${pageSize}`
            sql2 += ` WHERE userId = ?`
        }

        const [result1] = await mysqlPool.execute(sql1, params)
        const [result2] = await mysqlPool.execute(sql2, params)
        const result = await Promise.all([result1, result2])

        return { rows: result[0], total: result[1][0].total }
    }

    // 查询各个状态文章数量
    static async getArticleStateCount(userId) {
        let sql1 = `SELECT COUNT(*) AS total FROM article WHERE userId = ? AND state = 0`
        let sql2 = `SELECT COUNT(*) AS total FROM article WHERE userId = ? AND state = 1`
        let sql3 = `SELECT COUNT(*) AS total FROM article WHERE userId = ? AND state = 2`
        let sql4 = `SELECT COUNT(*) AS total FROM article WHERE userId = ? AND state = 3`
        let sql5 = `SELECT COUNT(*) AS total FROM article WHERE userId = ? AND state = 4`
        let sql6 = `SELECT COUNT(*) AS total FROM article WHERE userId = ? AND state = 5`

        let [result0] = await mysqlPool.execute(sql1, [userId])
        let [result1] = await mysqlPool.execute(sql2, [userId])
        let [result2] = await mysqlPool.execute(sql3, [userId])
        let [result3] = await mysqlPool.execute(sql4, [userId])
        let [result4] = await mysqlPool.execute(sql5, [userId])
        let [result5] = await mysqlPool.execute(sql6, [userId])
        let result = await Promise.all([result0, result1, result2, result3, result4, result5])

        result = [
            { state: 0, name: '草稿', total: result[0][0].total },
            { state: 1, name: '待审核', total: result[1][0].total },
            { state: 2, name: '审核通过', total: result[2][0].total },
            { state: 3, name: '已发布', total: result[3][0].total },
            { state: 4, name: '审核拒绝', total: result[4][0].total },
            { state: 5, name: '已下架', total: result[5][0].total }
        ]

        return result
    }

    // 修改文章
    static async updateArticle(userId, articleId, title, abstract, content, classifyId, state, coverUrl) {
        // 1、查询用户文章是否存在，如果存在，查询这个文章所有相关信息
        let sql1 = `
            SELECT
                a.id AS articleId,
                a.title,
                a.state,
                a.userId,
                a.classifyId
            FROM
                article a
            WHERE
                a.id = ? AND a.userId = ?
        `

        let [result1] = await mysqlPool.execute(sql1, [articleId, userId])


        if (!result1 || result1.length === 0) {
            return {
                code: 500,
                data: null,
                msg: '该用户文章不存在！'
            }
        } else {
            // 2、判断该文章是否为草稿或者审核拒绝的文章
            if (result1[0].state !== 0 && result1[0].state !== 4) {
                return {
                    code: 500,
                    data: null,
                    msg: '此文章不可修改！'
                }
            } else {
                let sql2 = ''
                let params = []

                if (coverUrl) {
                    sql2 = `UPDATE article SET title = ?, classifyId = ?, content = ?, abstract = ?, state = ?, coverUrl = ? WHERE id = ? AND userId = ?`
                    params = [title, classifyId, content, abstract, state, coverUrl, articleId, userId]
                } else {
                    sql2 = `UPDATE article SET title = ?, classifyId = ?, content = ?, abstract = ?, state = ? WHERE id = ? AND userId = ?`
                    params = [title, classifyId, content, abstract, state, articleId, userId]
                }

                let [result3] = await mysqlPool.execute(sql2, params)
                return result3
            }
        }
    }

    // 修改文章状态
    static async updateArticleState(userId, articleId, state) {
        // 1、查询用户下是否存在该文章
        let sql1 = `SELECT COUNT(*) AS total FROM article WHERE id = ? AND userId = ?`
        let [result] = await mysqlPool.execute(sql1, [articleId, userId])

        if (result[0].total === 0) {
            return {
                code: 500,
                data: null,
                msg: '该文章不存在！'
            }
        } else {
            // 2、修改文章状态
            let sql2 = `UPDATE article SET state = ? WHERE id = ? AND userId = ?`
            let [result2] = await mysqlPool.execute(sql2, [state, articleId, userId])

            return result2
        }
    }

    // 查询用户文章信息
    static async getUserArticleInfo(userId, articleId) {
        let sql = `
            SELECT
                a.id AS articleId,
                a.title,
                a.content,
                a.abstract,
                a.state,
                DATE_FORMAT(a.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                a.classifyId,
                a.userId,
                a.coverUrl
            FROM
                article a
            WHERE
                a.id = ? AND a.userId = ?
        `

        let [result] = await mysqlPool.execute(sql, [articleId, userId])

        return result[0]
    }

    // 评论文章
    static async commentArticle(querys) {
        let canshu = JSON.parse(JSON.stringify(querys))

        // 1、查询用户文章是否存在
        let result1 = await Article.getUserArticleInfo(querys.uid, querys.articleId)

        delete querys.uid // 删除文章作者id，防止在插入数据的时候，将作者id添加进去了

        if (!result1 || result1.length === 0) {
            // 2、用户文章不存在
            return { code: 500, data: null, msg: '用户文章不存在！' }
        } else {
            try {
                // 3、用户文章存在，parentId 不存在，添加父评论；否则添加子评论
                let params = []
                let fileds = []
                let values = []

                for (let i in querys) {
                    if (querys[i]) {
                        fileds.push(i)
                        values.push('?')
                        params.push(querys[i])
                    }
                }

                let createTime = moment().format('YYYY-MM-DD HH:mm:ss')
                let sql2 = `INSERT INTO comment (${fileds.join(',')}, createTime) VALUES (${values.join(',')}, ?)`
                let arr = [...params, createTime]
                let [result2] = await mysqlPool.execute(sql2, arr)

                if (result2.affectedRows === 1) {
                    let sql3 = `
                        SELECT
                            c.id AS commentId,
                            c.articleId,
                            c.parentId,
                            c.content,
                            DATE_FORMAT(c.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                            c.userId,
                            c.picUrls,
                            c.rootId,
                            u.nickName AS nickName,
                            u.username AS username,
                            u.avatar AS avatar,
                            (SELECT COUNT(*) FROM like_comment lc WHERE lc.commentId = c.id AND lc.userId = ?) AS isLikeState,
                            pu.nickName AS parentUsername,
                            pu.id AS parentUserId
                        FROM
                            comment c
                        LEFT JOIN
                            users u ON c.userId = u.id
                        LEFT JOIN
                            comment p ON c.parentId = p.id
                        LEFT JOIN
                            users pu ON p.userId = pu.id
                        WHERE
                            c.id = ?
                    `

                    let [result3] = await mysqlPool.execute(sql3, [canshu.uid, result2.insertId])
                    return result3[0]
                }
                return { code: 500, data: null, msg: '服务器错误，请联系管理员！'}
            } catch (error) {
                console.log('捕获错误：', error)
                return { code: 500, data: null, msg: error}
            }
        }
    }

    // 获取文章评论列表
    static async getCommentList(userId, articleId, currentPage, pageSize) {
        // 只查询一级评论数据总数，并且 parentId 为 NULL
        let sql1 = `SELECT COUNT(*) AS total FROM comment WHERE articleId = ? AND parentId IS NULL AND (state = 1 OR state = 2)`

        // 查询所有一级父评论列表（带分页）
        let sql2 = `
            SELECT
                c.id AS commentId,
                c.articleId,
                c.parentId,
                c.content,
                DATE_FORMAT(c.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                c.userId,
                c.picUrls,
                c.rootId,
                u.nickName AS nickName,
                u.username AS username,
                u.avatar AS avatar,
                (SELECT COUNT(*) FROM comment c2 WHERE c2.rootId = c.id) AS replyCount,
                (SELECT COUNT(*) FROM like_comment lc WHERE lc.commentId = c.id AND lc.userId = ?) AS isLikeState
            FROM
                comment c
            LEFT JOIN users u ON c.userId = u.id
            WHERE
                c.articleId = ? AND c.parentId IS NULL AND (c.state = 1 OR c.state = 2)
            ORDER BY
                c.createTime DESC
            LIMIT ${(currentPage - 1) * pageSize}, ${pageSize}
        `

        const [result1] = await mysqlPool.execute(sql1, [articleId])
        const [result2] = await mysqlPool.execute(sql2, [userId, articleId])
        const result = await Promise.all([result1, result2])
        const rootIdArrs = result[1].map(x => x.commentId)
        const rootIds = rootIdArrs.join(',')

        // 查询这些父评论下的所有子评论（通过rootId）
        let sql3 = `
            SELECT 
                c.id AS commentId,
                c.articleId,
                c.parentId,
                c.rootId,
                c.content,
                DATE_FORMAT(c.createTime, '%Y-%m-%d %H:%i:%s') AS createTime,
                c.userId,
                c.picUrls,
                u.nickName AS nickName,
                u.username AS username,
                u.avatar AS avatar,
                (SELECT COUNT(*) FROM like_comment lc WHERE lc.commentId = c.id AND lc.userId = ?) AS isLikeState,
                pu.nickName AS parentUsername,
                pu.id AS parentUserId
            FROM 
                comment c
            LEFT JOIN 
                users u ON c.userId = u.id
            LEFT JOIN
                comment p ON c.parentId = p.id
            LEFT JOIN
                users pu ON p.userId = pu.id
            WHERE
                c.rootId IN (${rootIds}) AND c.parentId IS NOT NULL AND (c.state = 1 OR c.state = 2)
            ORDER BY 
                c.rootId, c.createTime ASC
        `

        // 当文章下的评论为空时，直接返回空结果
        if (result2.length === 0) {
            return { total: 0, rows: [] }
        }

        const [result3] = await mysqlPool.execute(sql3, [userId])
        const commentData = handleCommentData(result[1], result3)
        
        return { total: result[0][0].total, rows: commentData }
    }

    // 获取父子评论总数
    static async getCommentTotalCount(articleId) {
        let sql = `SELECT COUNT(*) AS total FROM comment WHERE articleId = ? AND (state = 1 OR state = 2)`
        let [result] = await mysqlPool.execute(sql, [articleId])

        return result
    }

    // 删除文章评论
    static async delComment(commentId, articleId, userId) {
        // 1、查询用户评论是否存在
        const [result1] = await mysqlPool.execute(
            `SELECT id AS commentId, articleId, userId, rootId FROM comment WHERE id = ? AND articleId = ? AND userId = ?`,
            [commentId, articleId, userId]
        )

        if (!result1 || result1.length === 0) {
            // 2、用户评论不存在
            return { code: 500, data: null, msg: '用户评论不存在！' }
        }

        // 3、用户评论存在，判断删除的是否是父评论
        if (!result1[0].rootId) {
            // 删除的是父评论，要将父评论和所有相关子评论删除
            let sql = `DELETE FROM comment WHERE id = ? OR rootId = ?`

            const [result] = await mysqlPool.execute(sql, [result1[0].commentId, result1[0].commentId])
            return result
        } else {
            // 删除的子评论，只需要删除自身即可
            const [result] = await mysqlPool.execute(`DELETE FROM comment WHERE id = ? AND articleId = ? AND userId = ?`, [commentId, articleId, userId])
            return result
        }
    }

    // 给文章点赞&取消点赞
    static async likeArticle(articleId, userId) {
        // 查询这篇文章是否存在
        let [result1] = await mysqlPool.execute(`SELECT COUNT(*) AS total FROM article WHERE id = ?`, [articleId])
        if (!result1 || result1[0].total === 0) {
            return { code: 500, data: null, msg: '文章不存在！' }
        }

        // 查询用户是否已经给这个文章点赞过
        let sql3 = `SELECT COUNT(*) AS total FROM likes WHERE articleId = ? AND userId = ?`
        let [result3] = await mysqlPool.execute(sql3, [articleId, userId])

        if (result3 && result3[0].total > 0) {
            // 已经点赞过，那就取消点赞
            let sql4 = `DELETE FROM likes WHERE articleId = ? AND userId = ?`
            await mysqlPool.execute(sql4, [articleId, userId])

            // 取消点赞成功后，往文章表更新点赞数
            let sql6 = `UPDATE article SET likeCount = likeCount - 1 WHERE id = ?`
            await mysqlPool.execute(sql6, [articleId])
            return { code: 200, data: null, msg: '已取消点赞！' }
        } else {
            // 往点赞表插入数据
            let sql2 = `INSERT INTO likes (articleId, userId) VALUES (?, ?)`
            await mysqlPool.execute(sql2, [articleId, userId])

            // 点赞成功后，往文章表更新点赞数
            let sql5 = `UPDATE article SET likeCount = likeCount + 1 WHERE id = ?`
            await mysqlPool.execute(sql5, [articleId])
            return { code: 200, data: null, msg: '点赞成功！' }
        }
    }

    // 查询用户是否给文章点赞了
    static async getLikeArticleState(articleId, userId) {
        let sql = `SELECT COUNT(*) AS total FROM likes WHERE articleId = ? AND userId = ?`
        let [data] = await mysqlPool.execute(sql, [articleId, userId])
        let result = false

        if (data && data[0].total > 0) {
            result = true
        }

        return { code: 200, data: result, msg: '查询成功！' }
    }

    // 给文章评论点赞&取消点赞
    static async likeComment(articleId, commentId, userId) {
        // 1、查询这篇文章是否存在
        let [result1] = await mysqlPool.execute(`SELECT COUNT(*) AS total FROM article WHERE id = ?`, [articleId])
        if (!result1 || result1[0].total === 0) {
            return { code: 500, data: null, msg: '文章不存在！' }
        }

        // 2、查询这篇评论是否存在
        let [result2] = await mysqlPool.execute(`SELECT COUNT(*) AS total FROM comment WHERE id = ?`, [commentId])
        if (!result2 || result2[0].total === 0) {
            return { code: 500, data: null, msg: '评论不存在！' }
        }

        // 3、查询用户是否已经给这个评论点赞过
        let sql3 = `SELECT COUNT(*) AS total FROM like_comment WHERE commentId = ? AND userId = ?`
        let [result3] = await mysqlPool.execute(sql3, [commentId, userId])

        if (result3 && result3[0].total > 0) {
            // 已经点赞过，那就取消点赞
            let sql4 = `DELETE FROM like_comment WHERE commentId = ? AND userId = ?`
            await mysqlPool.execute(sql4, [commentId, userId])
            return { code: 200, data: null, msg: '已取消点赞！' }
        } else {
            // 3、往点赞表插入数据
            let sql2 = `INSERT INTO like_comment (commentId, userId) VALUES (?, ?)`
            await mysqlPool.execute(sql2, [commentId, userId])
            return { code: 200, data: null, msg: '点赞成功！' }
        }
    }

    // 查询用户是否给评论点赞了
    static async getLikeCommentState(commentId, userId) {
        let sql = `SELECT COUNT(*) AS total FROM like_comment WHERE commentId = ? AND userId = ?`
        let [data] = await mysqlPool.execute(sql, [commentId, userId])
        let result = false

        if (data && data[0].total > 0) {
            result = true
        }
        
        return { code: 200, data: result, msg: '操作成功！' }
    }

    // 举报文章评论
    static async reportComment(articleId, commentId, content, userId) {
        // 1、查询评论是否存在
        let [result1] = await mysqlPool.execute(`SELECT COUNT(*) AS total FROM comment WHERE id = ?`, [commentId])

        if (!result1 || result1[0].total === 0) {
            return { code: 500, data: null, msg: '评论不存在！' }
        }

        // 2、查询用户是否已经举报过
        let sql2 = `SELECT COUNT(*) AS total FROM comment_report WHERE article_id = ? AND comment_id = ? AND user_id = ?`
        let [result2] = await mysqlPool.execute(sql2, [articleId, commentId, userId])

        if (result2 && result2[0].total > 0) {
            // 已经举报过，将举报次数 +1
            let sql3 = `UPDATE comment_report SET count = count + 1, content = ?, create_time = ? WHERE article_id = ? AND comment_id = ? AND user_id = ?`
            await mysqlPool.execute(sql3, [content, moment().format('YYYY-MM-DD HH:mm:ss'), articleId, commentId, userId])
            return { code: 200, data: null, msg: '举报成功！' }
        } else {
            // 3、插入数据
            let sql3 = `INSERT INTO comment_report (article_id, comment_id, content, user_id, create_time) VALUES (?, ?, ?, ?, ?)`
            await mysqlPool.execute(sql3, [articleId, commentId, content, userId, moment().format('YYYY-MM-DD HH:mm:ss')])
            // 4、修改评论状态 state = 2，被举报，待处理
            await mysqlPool.execute(`UPDATE comment SET state = 2 WHERE id = ?`, [commentId])
            return { code: 200, data: null, msg: '举报成功！' }
        }
    }
}

module.exports = Article
